package com.hzdracom.epubreader.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.hzdracom.epubreader.entity.BookInfoItem;

public class BookInfoDbAdapter {
    //	private static final String DATABASE_NAME = "bookinfo.db";
    private static final String DATABASE_TABLE = "epub3_bookinfos";
//	private static final int DATABASE_VERSION = 1;

    private SQLiteDatabase mDb;
    @SuppressWarnings("unused")
    private final Context mContext;

    public static final String KEY_ID = "_id";
    public static final String KEY_BOOKID = "bookid";
    public static final String KEY_BOOKNAME = "bookname";

    public static final String KEY_BOOKPATH = "bookpath";

    public static final String KEY_LASTCHAPTER = "lastchapterid";

    public static final String KEY_LASTCHARINDEX = "lastcharindex";

    public static final String KEY_COVERPATH = "coverpath";

    public static final String KEY_LASTREADTIME = "lastreadtime";

    public static final String KEY_AUTHOR = "author";

    public static final String KEY_ISTOPMOST = "istopmost";

    public static final String KEY_READPERCENT = "readpercent";
    public static final String KEY_ACCOUNT = "account";

    //	private BookInfoDBOpenHelper dbOpenHelper;
    private SQLiteOpenHelper dbOpenHelper;

    public BookInfoDbAdapter(Context context, SQLiteOpenHelper sqLiteOpenHelper) {
        mContext = context;
        dbOpenHelper = sqLiteOpenHelper;
    }
//	public BookInfoDbAdapter(Context context) {
//		mContext = context;
//		dbOpenHelper = new BookInfoDBOpenHelper(context, DATABASE_NAME, null,
//		                                        DATABASE_VERSION);
//	}

    public void close() {
        mDb.close();
    }

    public void open() throws SQLiteException {
        try {
            mDb = dbOpenHelper.getWritableDatabase();
        } catch (SQLiteException ex) {
            mDb = dbOpenHelper.getReadableDatabase();
        }
    }

    // insert a new task
    public long insertTask(BookInfoItem book) {
        ContentValues newTaskValues = new ContentValues();
        // assign values for each row
        newTaskValues.put(KEY_BOOKID, book.getBookId());
        newTaskValues.put(KEY_BOOKNAME, book.getBookName());
        newTaskValues.put(KEY_BOOKPATH, book.getBookPath());
        newTaskValues.put(KEY_COVERPATH, book.getCoverPath());
        newTaskValues.put(KEY_LASTCHAPTER, book.getLastChapterId());
        newTaskValues.put(KEY_LASTCHARINDEX, book.getLastCharIndex());
        newTaskValues.put(KEY_LASTREADTIME, book.getLastReadTime().getTime());
        newTaskValues.put(KEY_AUTHOR, book.getAuthor());
        newTaskValues.put(KEY_ISTOPMOST, book.getIsTopMost());
        newTaskValues.put(KEY_READPERCENT, book.getReadPercent());
        newTaskValues.put(KEY_ACCOUNT, book.getAccount());
        // insert row
        return mDb.insert(DATABASE_TABLE, null, newTaskValues);
    }

    public boolean removeTask(/*long rowIndex*/long bookId, String account) {
        return mDb.delete(DATABASE_TABLE, KEY_BOOKID + "=" + bookId + " and " + KEY_ACCOUNT + "=" + account, null) > 0;
//		return mDb.delete(DATABASE_TABLE, KEY_ID + "=" + rowIndex, null) > 0;
    }

    public boolean isStoreBookInfo(String bookPath, String account) {
        Cursor cursor = mDb.rawQuery("select * from " + DATABASE_TABLE
                + " where " + KEY_BOOKPATH + "='" + bookPath + "' and " + KEY_ACCOUNT + "=" + account, null);

        return cursor.getCount() > 0;
    }

    public boolean updateTask(long bookId, BookInfoItem book) {
        ContentValues newValue = new ContentValues();
        newValue.put(KEY_BOOKNAME, book.getBookName());
        newValue.put(KEY_BOOKPATH, book.getBookPath());
        newValue.put(KEY_COVERPATH, book.getCoverPath());
        newValue.put(KEY_LASTCHAPTER, book.getLastChapterId());
        newValue.put(KEY_LASTCHARINDEX, book.getLastCharIndex());
        newValue.put(KEY_LASTREADTIME, book.getLastReadTime().getTime());
        newValue.put(KEY_ISTOPMOST, book.getIsTopMost());
        newValue.put(KEY_AUTHOR, book.getAuthor());
        newValue.put(KEY_READPERCENT, book.getReadPercent());
        return mDb.update(DATABASE_TABLE, newValue, KEY_BOOKID + "=" + bookId + " and " + KEY_ACCOUNT + "=" + book.getAccount(),
                null) > 0;
    }
//	public boolean updateTask(long rowIndex, BookInfoItem book) {
//		ContentValues newValue = new ContentValues();
//		newValue.put(KEY_BOOKNAME, book.getBookName());
//		newValue.put(KEY_BOOKPATH, book.getBookPath());
//		newValue.put(KEY_COVERPATH, book.getCoverPath());
//		newValue.put(KEY_LASTCHAPTER, book.getLastChapterId());
//		newValue.put(KEY_LASTCHARINDEX, book.getLastCharIndex());
//		newValue.put(KEY_LASTREADTIME, book.getLastReadTime().getTime());
//		newValue.put(KEY_ISTOPMOST, book.getIsTopMost());
//		newValue.put(KEY_AUTHOR, book.getAuthor());
//		newValue.put(KEY_READPERCENT, book.getReadPercent());
//		return mDb.update(DATABASE_TABLE, newValue, KEY_ID + "=" + rowIndex,
//		                  null) > 0;
//	}

    public Cursor getAllItemsCursor() {
        return mDb.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_BOOKID, KEY_BOOKNAME,
                KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
                KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
                KEY_READPERCENT, KEY_ACCOUNT}, null, null, null, null, null);
    }

    public Cursor getAllItemsCursorSortByReadTime() {
        return mDb.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_BOOKID, KEY_BOOKNAME,
                KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
                KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
                KEY_READPERCENT, KEY_ACCOUNT}, null, null, null, null, KEY_LASTREADTIME
                + " DESC");
    }

    public Cursor getAllItemsCursorSortByTopMostReadTime() {
        return mDb.rawQuery("select * from " + DATABASE_TABLE + " order by "
                + KEY_ISTOPMOST + " DESC, " + KEY_LASTREADTIME + " DESC", null);
    }

    public List<BookInfoItem> getAllItems(boolean isTopmost) {
        List<BookInfoItem> result = new ArrayList<BookInfoItem>();
        Cursor cursor = null;
        if (isTopmost) {
            cursor = getAllItemsCursorSortByTopMostReadTime();
        } else {
            cursor = getAllItemsCursorSortByReadTime();
        }
        if (cursor.getCount() > 0) {

            while (cursor.moveToNext()) {
                BookInfoItem item = new BookInfoItem();
                item.setBookId(cursor.getLong(cursor.getColumnIndex(KEY_BOOKID)));
                item.setBookName(cursor.getString(cursor
                        .getColumnIndex(KEY_BOOKNAME)));
                item.setBookPath(cursor.getString(cursor
                        .getColumnIndex(KEY_BOOKPATH)));
                item.setCoverPath(cursor.getString(cursor
                        .getColumnIndex(KEY_COVERPATH)));
                item.setLastChapterId(cursor.getString(cursor
                        .getColumnIndex(KEY_LASTCHAPTER)));
                item.setLastCharIndex(cursor.getInt(cursor
                        .getColumnIndex(KEY_LASTCHARINDEX)));
                item.setLastReadTime(new Date(cursor.getLong(cursor
                        .getColumnIndex(KEY_LASTREADTIME))));
                item.setRecordId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
                item.setAuthor(cursor.getString(cursor
                        .getColumnIndex(KEY_AUTHOR)));
                item.setReadPercent(cursor.getDouble(cursor
                        .getColumnIndex(KEY_READPERCENT)));
                item.setIsTopMost(cursor.getInt(cursor
                        .getColumnIndex(KEY_ISTOPMOST)));
                item.setAccount(cursor.getString(cursor.getColumnIndex(KEY_ACCOUNT)));
                result.add(item);
            }
        }

        return result;
    }

    public Cursor setCursorToToDoItem(/*long rowIndex*/long bookId) throws SQLException {
        Cursor result = mDb.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_BOOKID,
                        KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
                        KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
                        KEY_READPERCENT, KEY_ACCOUNT}, KEY_BOOKID + "=" + bookId, null, null, null,
                null);
//		Cursor result = mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID,
//		                                                         KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
//		                                                         KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
//		                                                         KEY_READPERCENT }, KEY_ID + "=" + rowIndex, null, null, null,
//		                                                         null);
        if (result.getCount() == 0 || !result.moveToFirst()) {
//			throw new SQLException("No to do item found for row:" + bookId);
            return null;
        }
        return result;
    }

    public BookInfoItem getBookItem(/*long rowIndex*/long bookId, String account) throws SQLException {
        Cursor cursor = mDb.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_BOOKID,
                        KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
                        KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
                        KEY_READPERCENT, KEY_ACCOUNT}, KEY_BOOKID + "=" + bookId + " and " + KEY_ACCOUNT + "=" + account,
                null, null, null, null);
//		Cursor cursor = mDb.query(DATABASE_TABLE, new String[] { KEY_ID,KEY_BOOKID,
//		                                                         KEY_BOOKNAME, KEY_BOOKPATH, KEY_LASTCHAPTER, KEY_LASTCHARINDEX,
//		                                                         KEY_COVERPATH, KEY_LASTREADTIME, KEY_AUTHOR, KEY_ISTOPMOST,
//		                                                         KEY_READPERCENT }, KEY_ID + "=" + rowIndex, null, null, null,
//		                                                         null);
        if (cursor.getCount() == 0 || !cursor.moveToFirst()) {
//			throw new SQLException("No to do item found for row:" + bookId);
            return null;
        }
        BookInfoItem result = new BookInfoItem();
        result.setBookId(cursor.getLong(cursor.getColumnIndex(KEY_BOOKID)));
        result.setBookName(cursor.getString(cursor.getColumnIndex(KEY_BOOKNAME)));
        result.setBookPath(cursor.getString(cursor.getColumnIndex(KEY_BOOKPATH)));
        result.setCoverPath(cursor.getString(cursor
                .getColumnIndex(KEY_COVERPATH)));
        result.setLastChapterId(cursor.getString(cursor
                .getColumnIndex(KEY_LASTCHAPTER)));
        result.setLastCharIndex(cursor.getInt(cursor
                .getColumnIndex(KEY_LASTCHARINDEX)));
        result.setLastReadTime(new Date(cursor.getLong(cursor
                .getColumnIndex(KEY_LASTREADTIME))));
        result.setRecordId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
        result.setAuthor(cursor.getString(cursor.getColumnIndex(KEY_AUTHOR)));
        result.setReadPercent(cursor.getDouble(cursor
                .getColumnIndex(KEY_READPERCENT)));
        result.setIsTopMost(cursor.getInt(cursor.getColumnIndex(KEY_ISTOPMOST)));
        result.setAccount(cursor.getString(cursor.getColumnIndex(KEY_ACCOUNT)));
        return result;

    }

    public static final String DATATABLE_DROP = "DROP TABLE IF EXISTS " + DATABASE_TABLE;
    // SQL statement to create a new database
    public static final String DATATABLE_CREATE = "create table "
            + DATABASE_TABLE + " (" + KEY_ID
            + " integer primary key autoincrement , " + KEY_BOOKID
            + " integer , " + KEY_BOOKNAME
            + " text not null, " + KEY_BOOKPATH + " text not null, "
            + KEY_LASTCHAPTER + " text not null, " + KEY_LASTCHARINDEX
            + " integer, " + KEY_COVERPATH + " text not null, "
            + KEY_LASTREADTIME + " long, " + KEY_AUTHOR
            + " text not null, " + KEY_ISTOPMOST + " integer,"
            + KEY_READPERCENT + " double,"
            + KEY_ACCOUNT + " text not null" +
            ");";
//	private static class BookInfoDBOpenHelper extends SQLiteOpenHelper {
//
//		public BookInfoDBOpenHelper(Context context, String name,
//				CursorFactory factory, int version) {
//			super(context, name, factory, version);
//		}
//
//		@Override
//		public void onCreate(SQLiteDatabase db) {
//			db.execSQL(DATATABLE_CREATE);
//
//		}
//
//		@Override
//		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//			Log.w("TaskDBAdapter", "Upgrading from version " + oldVersion
//					+ " to " + newVersion);
//
//			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
//			onCreate(db);
//
//		}
//
//	}
}
